package cn.tanghom.support.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.tanghom.support.page.Pagination;
import cn.tanghom.support.page.PaginationThreadUtils;


/**
 * 是对NamedParameterJdbcTemplate的封装
 * @author Hunteron-cp
 *
 */
@Repository
public class BaseActiveRecordDao implements ActiveRecordDao,InitializingBean {

	//private DB db;
	
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	protected String dbType = "mysql";
	
	public void setDataBaseType(String type){
		dbType = type;
	}
	
	@Override
	public int saveOrUpdate(String tableName, Map<String, Object> paramMap) {		
		try{
			return (int) dbInsert(tableName,null,paramMap);
		}catch(DataAccessException e){
			//throw e;
			return 0;
		}
		//return 0;
		
		//return this.namedParameterJdbcTemplate.update(sql, paramMap);
	}

	@Override
	public int update(String tableName,Map<String, Object> updateValueMap, String... where) {		
		StringBuilder sql = new StringBuilder();		
		return dbUpdate(tableName, null, updateValueMap, where);
		
	}
	
	@Override
	public int delete(String tableName, String condition) {		
		String sql = String.format("DELETE FROM %s WHERE %s", tableName,condition);		
		return this.jdbcTemplate.update(sql);		
	}

	@Override
	public int count(String sql, Map<String, Object> paramMap) {
		return this.namedParameterJdbcTemplate.queryForObject(sql, paramMap, Integer.class);
	}
	
	@Override
	public int[] batchExecute(String sql, final List<Object[]> paramList) {
		return this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			// 返回更新的记录数
			@Override
			public int getBatchSize() {
				return paramList.size();
			}

			// 设置参数
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] objs = paramList.get(i);
				for (int j = 0, length = objs.length; j < length; j++) {
					ps.setObject(j + 1, objs[j]);
				}
			}
		});
	}

	@Override
	public List<Map<String, Object>> find(String sql,Map<String, Object> paramMap) {
		/* 获取分页情况 */
		Pagination pagination = PaginationThreadUtils.get();
		if (pagination == null) {
			pagination = new Pagination();
			PaginationThreadUtils.set(pagination);
			pagination.setCurrentPage(1);
		}
		if (pagination.getTotalSum() == 0) {
			String countSql = "SELECT COUNT(1) FROM (" + sql + ") TEMP_TABLE_";
			pagination.setTotalSum(this.count(countSql, paramMap));
		}
		int firstResult = (pagination.getCurrentPage() - 1) * pagination.getPageSize();
		/* 校验分页情况 */
		if (firstResult >= pagination.getTotalSum() || firstResult < 0) {
			firstResult = 0;
			pagination.setCurrentPage(1);
		}
		/* 如果总数返回0, 直接返回空 */
		if (pagination.getTotalSum() == 0) {
			return null;
		}
		if (dbType.equals("oracle")) {
			return this.queryForOracle(sql, paramMap);
		} else if (dbType.equals("mysql")) {
			return this.queryForMysql(sql, paramMap);
		} else{
			return this.queryForMysql(sql, paramMap);
		}
		
	}
	
	private List<Map<String, Object>> queryForOracle(String sql, Map<String, Object> paramMap) {
		Pagination pagination = PaginationThreadUtils.get();
		int firstResult = (pagination.getCurrentPage() - 1) * pagination.getPageSize();
		int maxResults = pagination.getPageSize();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM (");
		sb.append("SELECT TEMP_TABLE_.*, ROWNUM ROWNUM_ FROM (").append(sql).append(") TEMP_TABLE_");
		sb.append(" WHERE ROWNUM <= ").append(firstResult + maxResults).append(")");
		sb.append(" WHERE ROWNUM_ > ").append(firstResult);
		return this.findAll(sb.toString(), paramMap);
	}

	private List<Map<String, Object>> queryForMysql(String sql, Map<String, Object> paramMap) {
		Pagination pagination = PaginationThreadUtils.get();
		int firstResult = (pagination.getCurrentPage() - 1) * pagination.getPageSize();
		int maxResults = pagination.getPageSize();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM (").append(sql).append(") TEMP_TABLE_");
		sb.append(" LIMIT ").append(firstResult).append(", ").append(maxResults);
		return this.findAll(sb.toString(), paramMap);
	}

	

	@Override
	public List<Map<String, Object>> findAll(String sql, Map<String, Object> paramMap) {
		return this.namedParameterJdbcTemplate.query(sql, paramMap, new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData meta = rs.getMetaData();
				for (int i = 1, count = meta.getColumnCount(); i <= count; i++) {
					Object obj = rs.getObject(i);
					String columnLabel = meta.getColumnLabel(i).toUpperCase();
					if (obj instanceof java.sql.Clob) {
						java.sql.Clob clob = rs.getClob(i);
						map.put(columnLabel, clob.getSubString((long) 1, (int) clob.length()));
					} else if (obj instanceof java.sql.Date || obj instanceof java.sql.Timestamp) {
						java.sql.Timestamp timestamp = rs.getTimestamp(i);
						map.put(columnLabel, timestamp);
					} else {
						map.put(columnLabel, obj);
					}
				}
				return map;
			}
		});
	}
	
		
	public JdbcOperations getJdbcOperations(){
		return jdbcTemplate;
	}
	
	public NamedParameterJdbcOperations getNamedParameterJdbcOperations() {
		return namedParameterJdbcTemplate;
	}

	
	@Override
	public void afterPropertiesSet() throws Exception {
		// TODO Auto-generated method stub
		//db.open(jdbcTemplate.getDataSource());	
	}
	

	public int dbUpdate(String table, String[] fields,Map<String, Object> values, String... where) throws DataAccessException {	 
		Set<String> realfields;		
		if(fields==null){
			realfields = values.keySet();			
		}
		else{
			realfields = new TreeSet<String>();
			for(String f: fields){
				if(values.containsKey(f)){
					realfields.add(f);
				}
			}	
		}
	
		StringBuilder sql=new StringBuilder("UPDATE ");
		sql.append(table);
		sql.append(" SET ");
		final Object[] innerO = new Object[realfields.size()];
		int i=0;		
		for(String f: realfields){
			if(i!=0) sql.append(",");
			sql.append(f);
			sql.append("=? ");
			innerO[i] = values.get(f);
			i++;			
		}
		i=0;
		sql.append(" WHERE ");
		for(String k: where){
			if(i!=0) sql.append(" AND ");
			sql.append(k);
			sql.append(" ");			
			i++;			
		}				
		
	  final String innersql = sql.toString();	  
	  try {
	  	return getJdbcOperations().update(innersql,innerO);
	  } catch (DataAccessException e) {
	   e.printStackTrace();
	   throw e;
	  } 
	}
	
	/**
	 * 
	 * @param sql
	 * @param o
	 * @return inserted id
	 * @throws DataAccessException
	 */
	public long dbInsert(String sql, Object... o ) throws DataAccessException  {
	  final String innersql = sql;
	  final Object[] innerO = o;
	  KeyHolder keyHolder = new GeneratedKeyHolder();
	  try {
		getJdbcOperations().update(new PreparedStatementCreator() {
	    public PreparedStatement createPreparedStatement(Connection con)
	      throws SQLException {
		     PreparedStatement ps = con.prepareStatement(innersql,Statement.RETURN_GENERATED_KEYS);
		     for(int i = 0;i<innerO.length;i++){
		    	 ps.setObject(i+1, innerO[i]);
		     }
		     
		     return ps;
	    }
	   }, keyHolder);
	  } catch (DataAccessException e) {
	   e.printStackTrace();
	   throw e;
	  }
	  if(keyHolder.getKey()==null) return 0;
	  System.out.println("自动插入id============================" + keyHolder.getKey());
	  return keyHolder.getKey().longValue();
	}
	
	/**
	 * 
	 * @param table
	 * @param fields
	 * @param values
	 * @return  inserted id
	 * @throws DataAccessException
	 */
	public long dbInsert(String table, String[] fields, Map<String, Object> values) throws DataAccessException  {
		Set<String> realfields;		
		if(fields==null){
			realfields = values.keySet();
		}
		else{
			realfields = new TreeSet<String>();
			for(String f: fields){
				if(values.containsKey(f)){
					realfields.add(f);
				}
			}			
		}
		StringBuilder sql=new StringBuilder("INSERT ");
		sql.append(table);
		sql.append("(");
		final Object[] innerO = new Object[realfields.size()];
		int i=0;		
		for(String f: realfields){
			if(i!=0) sql.append(",");
			sql.append(f);
			innerO[i] = values.get(f);
			i++;			
		}
		i=0;
		sql.append(") VALUES(");
		for(String f: realfields){
			if(i!=0) sql.append(",");
			sql.append('?');			
			i++;			
		}		
		sql.append(")");
		
	  final String innersql = sql.toString();
	  
	  KeyHolder keyHolder = new GeneratedKeyHolder();
	  try {
		JdbcOperations jdbc =  getJdbcOperations();
	  	jdbc.update(new PreparedStatementCreator() {
	    public PreparedStatement createPreparedStatement(Connection con)
	      throws SQLException {
		     PreparedStatement ps = con.prepareStatement(innersql,Statement.RETURN_GENERATED_KEYS);
		     for(int i = 0;i<innerO.length;i++){
		    	 ps.setObject(i+1, innerO[i]);
		     }
		     
		     return ps;
	    }
	   }, keyHolder);
	  } catch (DataAccessException e) {
	   e.printStackTrace();
	   throw e;
	  }
	  if(keyHolder.getKey()==null) return 0;
	  System.out.println("自动插入id============================" + keyHolder.getKey());
	  return keyHolder.getKey().longValue();
	}
	
	
	public String dbWhereClause(Map<String, Object> keys,boolean isAnd,String table){
		StringBuilder sql = new StringBuilder();
		int i=0;	
		for(Map.Entry<String,Object> entry: keys.entrySet()){
			if(i!=0) sql.append(" AND ");
			if(table!=null && table.length()>0){
				sql.append(table);
				sql.append(".");
			}
			sql.append(entry.getKey());
			sql.append("=");
			Object v = entry.getValue();
			if(v instanceof Number){
				sql.append(v);
			}
			else{
				sql.append('\'');
				sql.append(v);
				sql.append('\'');
			}
			i++;			
		}	
	  return sql.toString();
	}
	
}
